﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Collections;
using GenericParsing;

namespace LenderClearingHouse
{
    //Class to access the database and copy data to the proper tables
    static class ImportDataAccess
    {
        public static SqlConnection GetConnection()
        {
            return Utilities.GetConnection();
        }

        public static void UpdateLvReoAssmts(string apn)
        {
          string cleanApn = apn.Replace("-", "");

          string url = string.Format(
                @"http://www.amgnv.com/parcelsearch_results_non_pop1.asp?menu=7&parcel={0}", cleanApn);
          string content = RequestClient.Get(url);

          string yesNo = "YES";
          if (content.Contains("Object moved"))
          {
            yesNo = "NO";
          }

          DataAccess da = new DataAccess();
          da.ExecuteSQLNonQuery(string.Format(
            "update LvReo_MasterList set Assmts = '{0}' where Apn = '{1}'", yesNo, apn));
        }

        //Copy Nevada Legal News DataTable to the Database
        public static void BulkCopyNevadaLegal(DataTable sourceTable)
        {
          sourceTable.Columns.Add("ZipCode");
          foreach (DataRow dr in sourceTable.Rows)
          {
            string[] propVal = dr["Property Location"].ToString().Split(',');
            if (propVal.Length > 0)
            {
              dr["Property Location"] = propVal[0];
              if (propVal.Length >= 2)
                dr["Property Location"] += ", " + propVal[1];

              if (propVal.Length >= 3)
                dr["ZipCode"] = propVal[2];
            }
          }

          DataAccess da = new DataAccess();

          foreach (DataRow dr in sourceTable.Rows)
          {
              try
              {
                  da.ExecuteProcedure("updateMasterListForNvLegalCsv",
                    new string[]
              {
                "@Trustee"
                ,"@TsNumber"
                ,"@Apn"
                ,"@PropertyLocation"
                ,"@AuctionDate"
                ,"@ListingStatus"
                ,"@EstimatedBid"
                ,"@DocumentNumber"
                ,"@DateRecorded"
	              ,"@Client"
                ,"@ZipCode"
                ,"@Source"
                ,"@EscrowCompany"
                ,"@Hoa"
                ,"@PrelistStatus"
                ,"@PurchasePrice"
                ,"@RehabCosts"
                ,"@MiscNotes"
              },
                    new object[]
              {
                dr["Trustee"].ToString()
                ,dr["TS #"].ToString()
                ,dr["Apn"].ToString()
                ,dr["Property Location"].ToString()
                ,dr["Auction Date"].ToString()
                ,"Unassigned"//dr["Listing Status"].ToString()
                ,dr[" Estimated Bid"].ToString().Replace("$", "")
                ,dr["Document Number"].ToString()
                //,dr["Date Recorded"].ToString()
                ,dr["Date of Reservation"].ToString()                
	              ,dr["Client ID"].ToString()
                ,dr["ZipCode"].ToString()
                ,dr["Source"].ToString()
                ,dr["Company"].ToString()
                ,dr["Hoa"].ToString()
                ,dr["Prelist Status"].ToString()
                ,dr["Purchase Price"].ToString()
                ,dr["Rehab Costs"].ToString()
                ,dr["Misc Notes"].ToString()
              });
              }
              catch (Exception ex)
              {
                  throw new Exception("Error importing record with APN " + dr["Apn"].ToString() + " (" + ex.Message + ")");
              }
          }
        }

        //Copy Nevada Legal News DataTable to the Database
        public static void BulkCopyParcellAppraisal(DataTable sourceTable)
        {
          DataAccess da = new DataAccess();

          foreach (DataRow dr in sourceTable.Rows)
          {
            da.ExecuteProcedure("updateMasterListForScraping",
              new string[]
              {
                "@Apn"
                ,"@Stories"
                ,"@ConstructYear"
                ,"@TotalSQft"
                ,"@LotSize"
                ,"@Bedrooms"
                ,"@Bathrooms"
                ,"@Pool"
                ,"@Spa"
                ,"@LandUse"
                ,"@AssessorDesc"
                ,"@FirstFloorSqft"
                ,"@SecondFloorSqft"
                ,"@GarageSqft"
              },
              new object[]
              {
                dr["Apn"].ToString()
                ,dr["Stories"].ToString()
                ,dr["ConstructYear"].ToString()
                ,dr["TotalSQft"].ToString()
                ,dr["LotSize"].ToString()
                ,dr["Bedrooms"].ToString()
                ,dr["Bathrooms"].ToString()
                ,dr["Pool"].ToString()
                ,dr["Spa"].ToString()
                ,dr["LandUse"].ToString()
                ,dr["AssessorDesc"].ToString()
                ,dr["FirstFloorSqft"].ToString()
                ,dr["SecondFloorSqft"].ToString()
                ,dr["GarageSqft"].ToString()
              });
          }

            //SqlConnection myConnection = GetConnection();
            
            //try
            //{
            //    using (SqlBulkCopy s = new SqlBulkCopy(myConnection, SqlBulkCopyOptions.TableLock, null))
            //    {
            //        s.DestinationTableName = "ParcellAppraisal"; //Sql database table name
            //        s.BatchSize = sourceTable.Rows.Count;

            //        //map the database columns to DataTable
            //        s.ColumnMappings.Add("APN", "Apn");
            //        s.ColumnMappings.Add("AssessorDesc", "AssessorDesc");
            //        s.ColumnMappings.Add("LotSize", "LotSize");
            //        s.ColumnMappings.Add("ConstructYear", "ConstructYear");
            //        s.ColumnMappings.Add("LandUse", "LandUse");
            //        s.ColumnMappings.Add("TotalSqft", "TotalSqft");
            //        s.ColumnMappings.Add("FirstFloorSqft", "FirstFloorSqft");
            //        s.ColumnMappings.Add("Stories", "Stories");
            //        s.ColumnMappings.Add("Pool", "Pool");
            //        s.ColumnMappings.Add("SecondFloorSqft", "SecondFloorSqft");
            //        s.ColumnMappings.Add("Bedrooms", "Bedrooms");
            //        s.ColumnMappings.Add("Spa", "Spa");
            //        s.ColumnMappings.Add("Bathrooms", "Bathrooms");
            //        s.ColumnMappings.Add("GarageSqft", "GarageSqft");

            //        myConnection.Open(); // this may fix the exception
            //        s.WriteToServer(sourceTable); //write to server
            //        s.Close();
            //    }
            //}
            //catch (Exception ex)
            //{
            //    throw ex;
            //}
            //finally
            //{
            //    myConnection.Close();
            //}
        }

        //Copy Nevada Legal News DataTable to the Database
        public static void BulkCopyLVREOParcellAppraisal(DataTable sourceTable)
        {
          DataAccess da = new DataAccess();

          foreach (DataRow dr in sourceTable.Rows)
          {
            da.ExecuteProcedure("updateLVReo_MasterListForScraping",
              new string[]
              {
                "@Apn"
                ,"@Stories"
                ,"@ConstructYear"
                ,"@TotalSQft"
                ,"@LotSize"
                ,"@Bedrooms"
                ,"@Bathrooms"
                ,"@Pool"
                ,"@Spa"
                ,"@LandUse"
                ,"@AssessorDesc"
                ,"@FirstFloorSqft"
                ,"@SecondFloorSqft"
                ,"@GarageSqft"
              },
              new object[]
              {
                dr["Apn"].ToString()
                ,dr["Stories"].ToString()
                ,dr["ConstructYear"].ToString()
                ,dr["TotalSQft"].ToString()
                ,dr["LotSize"].ToString()
                ,dr["Bedrooms"].ToString()
                ,dr["Bathrooms"].ToString()
                ,dr["Pool"].ToString()
                ,dr["Spa"].ToString()
                ,dr["LandUse"].ToString()
                ,dr["AssessorDesc"].ToString()
                ,dr["FirstFloorSqft"].ToString()
                ,dr["SecondFloorSqft"].ToString()
                ,dr["GarageSqft"].ToString()
              });
          }

          /*
            SqlConnection myConnection = GetConnection();

            bool success = false;
            try
            {
                using (SqlBulkCopy s = new SqlBulkCopy(myConnection, SqlBulkCopyOptions.TableLock, null))
                {
                    s.DestinationTableName = "LvReo_ParcellAppraisal"; //Sql database table name
                    s.BatchSize = sourceTable.Rows.Count;

                    //map the database columns to DataTable
                    s.ColumnMappings.Add("APN", "Apn");
                    s.ColumnMappings.Add("AssessorDesc", "AssessorDesc");
                    s.ColumnMappings.Add("LotSize", "LotSize");
                    s.ColumnMappings.Add("ConstructYear", "ConstructYear");
                    s.ColumnMappings.Add("LandUse", "LandUse");
                    s.ColumnMappings.Add("TotalSqft", "TotalSqft");
                    s.ColumnMappings.Add("FirstFloorSqft", "FirstFloorSqft");
                    s.ColumnMappings.Add("Stories", "Stories");
                    s.ColumnMappings.Add("Pool", "Pool");
                    s.ColumnMappings.Add("SecondFloorSqft", "SecondFloorSqft");
                    s.ColumnMappings.Add("Bedrooms", "Bedrooms");
                    s.ColumnMappings.Add("Spa", "Spa");
                    s.ColumnMappings.Add("Bathrooms", "Bathrooms");
                    s.ColumnMappings.Add("GarageSqft", "GarageSqft");

                    myConnection.Open(); // this may fix the exception
                    s.WriteToServer(sourceTable); //write to server
                    s.Close();
                }
                success = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                myConnection.Close();
            }

            return success;
           */
        }

        //Copy Recon Trust DataTable to the Database
        public static void BulkCopyOpeningBid(DataTable sourceTable)
        {
          DataAccess da = new DataAccess();

          foreach (DataRow dr in sourceTable.Rows)
          {
            da.ExecuteProcedure("updateMasterListForOpeningBid",
              new string[]
              {
                "@TsNumber"
                ,"@TsOpeningBid"
                ,"@ReconTrust_DateOfSale"
                ,"@NdsCorp_SaleDate"
              },
              new object[]
              {
                dr["TsNumber"].ToString()
                ,dr["OpeningBid"].ToString()
                ,sourceTable.Columns.Contains("DateOfSale") ? dr["DateOfSale"].ToString() : null
                ,sourceTable.Columns.Contains("NdsCorp_SaleDate") ? dr["NdsCorp_SaleDate"].ToString() : null
              });
          }

          /*
            SqlConnection myConnection = GetConnection();

            bool success = false;
            try
            {
                using (SqlBulkCopy s = new SqlBulkCopy(myConnection, SqlBulkCopyOptions.TableLock, null))
                {
                    s.DestinationTableName = "OpeningBids"; //Sql database table name
                    s.BatchSize = sourceTable.Rows.Count;

                    //map the database columns to DataTable
                    s.ColumnMappings.Add("TsNumber", "TsNumber");
                    s.ColumnMappings.Add("OpeningBid", "OpeningBid");
                    s.ColumnMappings.Add("ScrapeSource", "ScrapeSource");


                    myConnection.Open(); // this may fix the exception
                    s.WriteToServer(sourceTable); //write to server
                    s.Close();
                }
                success = true;
            }
            catch (Exception ex)
            {
                throw;
            }
            finally
            {
                myConnection.Close();
            }

            return success;
           */
        }

        //Copy Recon Trust DataTable to the Database
        public static void BulkCopyOpeningBidApn(DataTable sourceTable)
        {
          DataAccess da = new DataAccess();

          foreach (DataRow dr in sourceTable.Rows)
          {
            da.ExecuteProcedure("updateMasterListForOpeningBid",
              new string[]
              {
                "@Apn"
                ,"@TsOpeningBid"
                ,"@LPS_SalesStatus"
                ,"@PriorityPost_Status"
              },
              new object[]
              {
                dr["Apn"].ToString()
                ,dr["OpeningBid"].ToString()
                ,sourceTable.Columns.Contains("SalesStatus") ? dr["SalesStatus"].ToString() : null
                ,sourceTable.Columns.Contains("PriorityPost_Status") ? dr["PriorityPost_Status"].ToString() : null
              });
          }
        }

        //Returns the master list of Apn numbers from Nevada Legal
        public static string[] getApnMasterlist()
        {
            SqlConnection myConnection = GetConnection();
            string sql = @"Select APN from MasterList;";
            string[] apnNumbers;
            
            try
            {
                //Builds cmd obj from myConnection and set sql
                myConnection.Open();
                SqlCommand cmd = myConnection.CreateCommand(); 
                cmd.CommandText = sql;

                //Create new datatable and fill it with the result
                DataTable dt = new DataTable();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(dt);

                apnNumbers = new string[dt.Rows.Count];
                int i = 0;

                //Loop and store values
                foreach (DataRow myRow in dt.Rows)
                {
                    foreach (DataColumn myColumn in dt.Columns)
                    {
                        apnNumbers[i] = myRow[myColumn].ToString();
                        i++;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                myConnection.Close();
            }
            return apnNumbers;
        }

        public static string[] getApnWorkinglist()
        {
          SqlConnection myConnection = GetConnection();
          string sql = @"Select APN from WorkingList;";
          string[] apnNumbers;

          try
          {
            //Builds cmd obj from myConnection and set sql
            myConnection.Open();
            SqlCommand cmd = myConnection.CreateCommand();
            cmd.CommandText = sql;

            //Create new datatable and fill it with the result
            DataTable dt = new DataTable();
            SqlDataAdapter adap = new SqlDataAdapter(cmd);
            adap.Fill(dt);

            apnNumbers = new string[dt.Rows.Count];
            int i = 0;

            //Loop and store values
            foreach (DataRow myRow in dt.Rows)
            {
              foreach (DataColumn myColumn in dt.Columns)
              {
                apnNumbers[i] = myRow[myColumn].ToString();
                i++;
              }
            }
          }
          catch (Exception ex)
          {
            throw ex;
          }
          finally
          {
            myConnection.Close();
          }
          return apnNumbers;
        }

        //Returns the master list of Apn numbers from Nevada Legal
        public static string[] getApnLVREOMasterlist()
        {
            SqlConnection myConnection = GetConnection();
            string sql = @"Select APN from LvReo_MasterList;";
            string[] apnNumbers;

            try
            {
                //Builds cmd obj from myConnection and set sql
                myConnection.Open();
                SqlCommand cmd = myConnection.CreateCommand();
                cmd.CommandText = sql;

                //Create new datatable and fill it with the result
                DataTable dt = new DataTable();
                SqlDataAdapter adap = new SqlDataAdapter(cmd);
                adap.Fill(dt);

                apnNumbers = new string[dt.Rows.Count];
                int i = 0;

                //Loop and store values
                foreach (DataRow myRow in dt.Rows)
                {
                    foreach (DataColumn myColumn in dt.Columns)
                    {
                        apnNumbers[i] = myRow[myColumn].ToString();
                        i++;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                myConnection.Close();
            }
            return apnNumbers;
        }

        public static string[] getApnSDREOMasterlist()
        {
          SqlConnection myConnection = GetConnection();
          string sql = @"Select APN from SdReo_MasterList;";
          string[] apnNumbers;

          try
          {
            //Builds cmd obj from myConnection and set sql
            myConnection.Open();
            SqlCommand cmd = myConnection.CreateCommand();
            cmd.CommandText = sql;

            //Create new datatable and fill it with the result
            DataTable dt = new DataTable();
            SqlDataAdapter adap = new SqlDataAdapter(cmd);
            adap.Fill(dt);

            apnNumbers = new string[dt.Rows.Count];
            int i = 0;

            //Loop and store values
            foreach (DataRow myRow in dt.Rows)
            {
              foreach (DataColumn myColumn in dt.Columns)
              {
                apnNumbers[i] = myRow[myColumn].ToString();
                i++;
              }
            }
          }
          catch (Exception ex)
          {
            throw ex;
          }
          finally
          {
            myConnection.Close();
          }
          return apnNumbers;
        }

        //Clears the Parcell Appraisal data before refreshing
        public static void ClearParsellAppraisal()
        {
            SqlConnection myConnection = GetConnection();
            string sql = @"DELETE FROM ParcellAppraisal;";
            SqlCommand myCommand = new SqlCommand(sql, myConnection);
            myCommand.CommandType = CommandType.Text;

            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw;
            }
            finally
            {
                myConnection.Close();
            }
        }

        //Clears the Nevada Legal data before refreshing
        public static void ClearNevadaLegal()
        {
            SqlConnection myConnection = GetConnection();
            string sql = @"Delete from MasterList;";
            SqlCommand myCommand = new SqlCommand(sql, myConnection);
            myCommand.CommandType = CommandType.Text;

            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw;
            }
            finally
            {
                myConnection.Close();
            }
        }

        //Clear Recon Trust entries from the OpeningBids table
        public static void ClearReconTrust()
        {
            SqlConnection myConnection = GetConnection();
            string sql = @"DELETE FROM OpeningBids where ScrapeSource='RECON';";
            SqlCommand myCommand = new SqlCommand(sql, myConnection);
            myCommand.CommandType = CommandType.Text;

            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw;
            }
            finally
            {
                myConnection.Close();
            }
        }

        //Clear Lps Asap entries from the OpeningBids table
        public static void ClearLpsAsap()
        {
            SqlConnection myConnection = GetConnection();
            string sql = @"DELETE FROM OpeningBidsApn where ScrapeSource='LPS';";
            SqlCommand myCommand = new SqlCommand(sql, myConnection);
            myCommand.CommandType = CommandType.Text;

            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw;
            }
            finally
            {
                myConnection.Close();
            }
        }

        //Clear NdsCorp entries from the OpeningBids table
        public static void ClearNdsCorp()
        {
            SqlConnection myConnection = GetConnection();
            string sql = @"DELETE FROM OpeningBids where ScrapeSource='NdsCorp';";
            SqlCommand myCommand = new SqlCommand(sql, myConnection);
            myCommand.CommandType = CommandType.Text;

            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw;
            }
            finally
            {
                myConnection.Close();
            }
        }

        //Clear NdsCorp entries from the OpeningBids table
        public static void ClearRpsSales()
        {
            SqlConnection myConnection = GetConnection();
            string sql = @"DELETE FROM OpeningBids where ScrapeSource='RpsSales';";
            SqlCommand myCommand = new SqlCommand(sql, myConnection);
            myCommand.CommandType = CommandType.Text;

            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw;
            }
            finally
            {
                myConnection.Close();
            }
        }

        //Clear Lps Asap entries from the OpeningBids table
        public static void ClearPriorityPosting()
        {
            SqlConnection myConnection = GetConnection();
            string sql = @"DELETE FROM OpeningBidsApn where ScrapeSource='PriorPost';";
            SqlCommand myCommand = new SqlCommand(sql, myConnection);
            myCommand.CommandType = CommandType.Text;

            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw;
            }
            finally
            {
                myConnection.Close();
            }
        }

        //Clear OpeningBids Table
        public static void ClearAllBids()
        {
            SqlConnection myConnection = GetConnection();
            string sql = @"DELETE FROM OpeningBids;";
            SqlCommand myCommand = new SqlCommand(sql, myConnection);
            myCommand.CommandType = CommandType.Text;

            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw;
            }
            finally
            {
                myConnection.Close();
            }
        }

        //Clear OpeningBidsApn
        public static void ClearAllBidsApn()
        {
            SqlConnection myConnection = GetConnection();
            string sql = @"DELETE FROM OpeningBidsApn;";
            SqlCommand myCommand = new SqlCommand(sql, myConnection);
            myCommand.CommandType = CommandType.Text;

            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw;
            }
            finally
            {
                myConnection.Close();
            }
        }

        //Executes stored procedure
        public static int ExecuteSQLNonQuery(string sql)
        {
            int rows = 0; //number of rows modified
            SqlConnection myConnection = GetConnection();
            //Set stored procedure name and command type
            SqlCommand myCommand = new SqlCommand(sql, myConnection);
            myCommand.CommandType = CommandType.Text;

            try
            {
                myConnection.Open();
                rows = (int)myCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                //Utilities.LogError(ex);
                throw ex;
            }
            finally
            {
                myConnection.Close();
            }
            return rows;
        }

        static DataTable GetDataTableFromCsv(string path, bool isFirstRowHeader)
        {
          var adapter = new GenericParsing.GenericParserAdapter(path);
          adapter.FirstRowHasHeader = true;
          return adapter.GetDataTable();
        }

        /*
         * 
         * This section contains all of the methods and functions
         * used to import csv files to the database
         *
         */

        //Import into the Master List new parser for csv files
        public static void ImportNevadaLegalCSV(string filepath)
        {
            string result = "Import failed";
            bool status = false;
            ArrayList colvals;
            string[] propVal = new string[3];
            string split = "";
            string strLineText = string.Empty;
            bool eatLine = true;
            int line = 0;

            //Initialize tables
            //DataTable nlTable = ImportDataBuilders.NevadaLegalNews();
            DataTable nlTable = GetDataTableFromXls(filepath);
            

          /*
            List<string> apns = new List<string>();
            apns.AddRange(getApnMasterlist());

            try
            {
                //Build row objects for tables
                DataRow nlrow; 
                using (StreamReader objReader = new StreamReader(filepath))
                {
                    while ((strLineText = objReader.ReadLine()) != null)
                    {
                        if (eatLine.Equals(false))
                        {
                            colvals = CSVParser(strLineText); //clean up rows
                            line++;
                            //Build new rows to add to the tables
                            nlrow = nlTable.NewRow();

                            //Add values to mlTable Table Row
                            nlrow["ListingStatus"] = colvals[0];
                            nlrow["AuctionDate"] = colvals[1];
                            nlrow["SaleLocation"] = colvals[2];
                            nlrow["TsNumber"] = colvals[3];
                            nlrow["APN"] = colvals[4];
                            if (apns.IndexOf(nlrow["APN"].ToString()) >= 0) continue;

                            nlrow["ClientId"] = colvals[5];
                            nlrow["EstimatedBid"] = colvals[6];
                            nlrow["SoldPrice"] = colvals[7];
                            nlrow["Trustee"] = colvals[8];
                            nlrow["DateRecorded"] = colvals[11];
                            nlrow["DocumentNumber"] = colvals[12];
                            split = colvals[13].ToString();
                            propVal = split.Split(',');
                            if (propVal.Length > 0)
                            {
                              nlrow["PropertyLocation"] = propVal[0];
                              if(propVal.Length >= 2)
                                nlrow["PropertyLocation"] += ", " + propVal[1];

                              if (propVal.Length >= 3)
                                nlrow["ZipCode"] = propVal[2];
                            }

                            //Add the rows to their table
                            nlTable.Rows.Add(nlrow);
                            status = true;
                            //colvals.Clear(); propVal[0] = ""; propVal[1] = ""; propVal[2] = ""; split = "";
                        }
                        else { eatLine = false; }
                    }
                }//end the using statement
            }
            catch (Exception ex)
            {
              throw new Exception("Error at line " + line + "<br/>" + ex.Message);
            }
          */

          BulkCopyNevadaLegal(nlTable);
          result = "Csv file imported to Master List.";
        }

        //Import into the Master List new parser for csv files
        public static void ImportMasterListCSV(string filepath)
        {
            string result = "Import failed";
            bool status = false; 
            ArrayList colvals;
            string strLineText = string.Empty;
            bool eatLine = true;
            int line = 0;

            //Initialize tables with import format
            DataTable mlTable = ImportDataBuilders.CreateMasterTableCSV();
            DataTable parcelTable = ImportDataBuilders.CreateParcellTableCSV();
            DataTable apnTable = ImportDataBuilders.CreateApnCSV();
            DataTable tsTable = ImportDataBuilders.CreateTsBidCSV();
            
          List<string> apns = new List<string>();
          apns.AddRange(getApnMasterlist());

            try
            {
                //Build row objects for tables
                DataRow mlrow; DataRow parcelrow; DataRow apnrow; DataRow tsrow;
                int columnsCount = 0;

                using (StreamReader objReader = new StreamReader(filepath))
                {
                    while ((strLineText = objReader.ReadLine()) != null)
                    {
                        if (eatLine.Equals(false))
                        {
                            colvals = CSVParser(strLineText); //clean up rows
                          line++;

                            if (colvals.Count < columnsCount)
                            {
                              throw new Exception(string.Format(
                                "The record #{0} has insufficient number of fields: {1} fields are expected; {2} fields are found. Please verify the file and import it again."
                                , line
                                , columnsCount
                                , colvals.Count));
                            }
                            
                            //Build new rows to add to the tables
                            mlrow = mlTable.NewRow(); parcelrow = parcelTable.NewRow(); apnrow = apnTable.NewRow(); tsrow = tsTable.NewRow();

                            //Add values to mlTable Table Row
                            mlrow["Trustee"] = colvals[1];
                            mlrow["TsNumber"] = colvals[2];
                            mlrow["APN"] = colvals[3];
                            if (apns.IndexOf(mlrow["APN"].ToString()) >= 0) continue;
                            apns.Add(mlrow["APN"].ToString());

                            mlrow["PropertyLocation"] = colvals[4];
                            mlrow["ZipCode"] = colvals[5];
                            mlrow["vo"] = colvals[6];
                            mlrow["hoa"] = colvals[8];
                            mlrow["AuctionDate"] = colvals[16];
                            mlrow["ListingStatus"] = "Unassigned";//colvals[17];
                            mlrow["EstimatedBid"] = colvals[18];
                            mlrow["MaxBid"] = colvals[20];
                            mlrow["EstVal"] = colvals[21];
                            mlrow["Tax"] = colvals[22];
                            mlrow["Nos"] = colvals[23];
                            mlrow["Liens"] = colvals[24];
                            mlrow["OnOff"] = colvals[26];
                            mlrow["PropertyDetails"] = colvals[27];

                            mlrow["Source"] = colvals[33];
                            mlrow["DateRecorded"] = colvals[34];
                            mlrow["EscrowCompany"] = colvals[35];
                            mlrow["PrelistStatus"] = colvals[36];
                            mlrow["PurchasePrice"] = colvals[37];
                            mlrow["RehabCosts"] = colvals[38];
                            mlrow["MiscNotes"] = colvals[39];

                            //Add values to parcelTable Row
                            parcelrow["APN"] = colvals[3];
                            parcelrow["Stories"] = colvals[7];
                            parcelrow["ConstructYear"] = colvals[9];
                            parcelrow["TotalSqft"] = colvals[10];
                            parcelrow["LotSize"] = colvals[11];
                            parcelrow["Bedrooms"] = colvals[12];
                            parcelrow["Bathrooms"] = colvals[13];
                            parcelrow["GarageSqft"] = colvals[14];
                            parcelrow["Pool"] = colvals[15];
                            parcelrow["AssessorDesc"] = colvals[28];

                            //Add values to apnTable Row
                            apnrow["Apn"] = colvals[3];
                            apnrow["OpeningBid"] = colvals[19];
                            apnrow["ScrapeSource"] = "CSVIMPORT";

                            //Add values to TsTable Row
                            tsrow["TsNumber"] = colvals[2];
                            tsrow["OpeningBid"] = colvals[19];
                            tsrow["ScrapeSource"] = "CSVIMPORT";

                            //Add the rows to their table
                            mlTable.Rows.Add(mlrow); parcelTable.Rows.Add(parcelrow); apnTable.Rows.Add(apnrow); tsTable.Rows.Add(tsrow);
                            status = true;
                            colvals.Clear();
                        }
                        else
                        {
                          eatLine = false;
                          columnsCount = strLineText.Split(',').Length;
                        }                        
                    }
                }//end the using statement
            }
            catch (Exception ex)
            {
                status = false;
                throw new Exception("Error at record " + line + "<br/>" + ex.Message);
            }

            //Call the bulk insert functions here
            if (status.Equals(true))
            {
                //Check for valid returns
                if (BulkInsertMlTableCSV(mlTable) && BulkInsertParcellTableCSV(parcelTable) && BulkInsertApnTableCSV(apnTable) && BulkInsertTsTableCSV(tsTable))
                {
                    result = "Csv file imported to Master List.";
                }
            }
        }

        //Import into the Master List new parser for csv files
        public static void ImportWorkingListCSV(string filepath)
        {
            string result = "Import failed";
            bool status = false;
            ArrayList colvals;
            string strLineText = string.Empty;
            int PropertyId = 1; //Artificial Property Id
            bool eatLine = true;
            int line = 0;

            //Initialize tables with import format
            DataTable wlTable = ImportDataBuilders.CreateWorkingListTableCSV();

            List<string> apns = new List<string>();
            apns.AddRange(getApnWorkinglist());

            try
            {
                //Build row objects for tables
                DataRow wlrow; 

                using (StreamReader objReader = new StreamReader(filepath))
                {
                    while ((strLineText = objReader.ReadLine()) != null)
                    {
                        if (eatLine.Equals(false))
                        {
                            colvals = CSVParser(strLineText); //clean up rows
                            line++;
                            //Build new rows to add to the tables
                            wlrow = wlTable.NewRow();

                            //Add values to mlTable Table Row
                            wlrow["PropertyId"] = PropertyId;
                            wlrow["Trustee"] = colvals[1];
                            wlrow["TsNumber"] = colvals[2];
                            wlrow["APN"] = colvals[3];
                            if (apns.IndexOf(wlrow["APN"].ToString()) >= 0) continue;

                            wlrow["PropertyLocation"] = colvals[4];
                            wlrow["ZipCode"] = colvals[5];
                            wlrow["vo"] = colvals[6];
                            wlrow["Stories"] = colvals[7];
                            wlrow["hoa"] = colvals[8];
                            wlrow["ConstructYear"] = colvals[9];
                            wlrow["TotalSqft"] = colvals[10];
                            wlrow["LotSize"] = colvals[11];
                            wlrow["Bedrooms"] = colvals[12];
                            wlrow["Bathrooms"] = colvals[13];
                            wlrow["GarageSqft"] = colvals[14];
                            wlrow["Pool"] = colvals[15];
                            wlrow["AuctionDate"] = colvals[16];
                            wlrow["ListingStatus"] = "Unassigned";//colvals[17];
                            wlrow["EstimatedBid"] = colvals[18];
                            wlrow["OpeningBid"] = colvals[19];
                            wlrow["MaxBid"] = colvals[20];
                            wlrow["EstVal"] = colvals[21];
                            wlrow["Tax"] = colvals[22];
                            wlrow["Nos"] = colvals[23];
                            wlrow["Liens"] = colvals[24];
                            wlrow["OnOff"] = colvals[26];
                            wlrow["PropertyDetails"] = colvals[27];
                            wlrow["AssessorDesc"] = colvals[28];

                            wlrow["Source"] = colvals[33];
                            wlrow["DateRecorded"] = colvals[34];
                            wlrow["EscrowCompany"] = colvals[35];
                            wlrow["PrelistStatus"] = colvals[36];
                            wlrow["PurchasePrice"] = colvals[37];
                            wlrow["RehabCosts"] = colvals[38];
                            wlrow["MiscNotes"] = colvals[39];

                            PropertyId++;
                            //Add the rows to their table
                            wlTable.Rows.Add(wlrow);
                            status = true;
                            colvals.Clear();
                        }
                        else { eatLine = false; }
                    }
                }//end the using statement
            }
            catch (Exception ex)
            {
                status = false;
                throw new Exception("Error at line " + line + "<br/>" + ex.Message);
            }

            //Call the bulk insert functions here
            if (status.Equals(true))
            {
                //Check for valid returns
                if (BulkInsertWlTableCSV(wlTable))
                {
                    result = "Csv file imported to Working List.";
                }
            }
        }

        static DataTable GetDataTableFromXls(string fileName)
        {
          using (OleDbConnection conn = new OleDbConnection(
            string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;IMEX=1\"", fileName)))
          {
            OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [sheet1$]", conn);
            DataSet ds1 = new DataSet();
            adapter.Fill(ds1);

            return ds1.Tables[0];
          }
        }

        //Import the title info into the master list
        public static void ImportTitleInfoCSV(string filepath)
        {
            string result = "Import failed";
            ArrayList colvals;
            bool status = false;
            string strLineText = string.Empty;
            bool eatLine = true;
            int line = 0;

            DataTable tbl = GetDataTableFromXls(filepath);

            DataAccess da = new DataAccess();

            foreach (DataRow dr in tbl.Rows)
            {
              da.ExecuteProcedure("updateMasterListForTitleCompany",
                new string[]
                {
                  "@Apn"
                  ,"@Tax"
                  ,"@Nos"
                  ,"@Liens"
                },
                new object[]
                {
                  dr["Apn"].ToString()
                  ,dr["Tax_amt"].ToString()
                  ,dr["Nos"].ToString()
                  ,dr["Liens"].ToString()
                });
            }

          /*
            try
            {
                using (StreamReader objReader = new StreamReader(filepath))
                {
                    while ((strLineText = objReader.ReadLine()) != null)
                    {
                        line++;
                        if (eatLine.Equals(false))
                        {
                            strLineText = strLineText.Replace("'", "");
                            colvals = CSVParser(strLineText); //clean up rows
                            ExecuteSQLNonQuery("Update MasterList Set Liens = '" + colvals[3] + "' where apn ='" + colvals[0] + "'");
                            ExecuteSQLNonQuery("Update WorkingList Set Liens = '" + colvals[3] + "' where apn ='" + colvals[0] + "'");
                            status = true; colvals[0] = ""; colvals[3] = ""; 
                        }
                        else { eatLine = false; }
                    }
                }//end the using statement
            }
            catch (Exception ex)
            {
                status = false;
                throw new Exception("Error at line " + line + "<br/>" + ex.Message);
            }
           */
          result = "Matching properties updated";
        }

        //Import into the SDREO Master List new parser for csv files
        public static void ImportSdReoMasterListCSV(string filepath)
        {
            string result = "Import failed";
            bool status = false;
            ArrayList colvals;
            string strLineText = string.Empty;
            int PropertyId = 1; //Artificial Property Id
            bool eatLine = true;
            int line = 0;

            //Initialize tables with import format
            DataTable wlTable = ImportDataBuilders.CreateSdReoMasterListTableCSV();

            List<string> apns = new List<string>();
            apns.AddRange(getApnSDREOMasterlist());

            try
            {
                //Build row objects for tables
                DataRow wlrow;

                using (StreamReader objReader = new StreamReader(filepath))
                {
                    while ((strLineText = objReader.ReadLine()) != null)
                    {
                        if (eatLine.Equals(false))
                        {
                            colvals = CSVParser(strLineText); //clean up rows
                            line++;
                            //Build new rows to add to the tables
                            wlrow = wlTable.NewRow();

                            //Add values to mlTable Table Row
                            wlrow["PropertyId"] = PropertyId;
                            wlrow["Trustee"] = colvals[1];
                            wlrow["TsNumber"] = colvals[2];
                            wlrow["APN"] = colvals[3];
                            if (apns.IndexOf(wlrow["APN"].ToString()) >= 0) continue;

                            wlrow["PropertyLocation"] = colvals[4];
                            wlrow["ZipCode"] = colvals[5];
                            wlrow["vo"] = colvals[6];
                            wlrow["Stories"] = colvals[7];
                            wlrow["hoa"] = colvals[8];
                            wlrow["ConstructYear"] = colvals[9];
                            wlrow["TotalSqft"] = colvals[10];
                            wlrow["LotSize"] = colvals[11];
                            wlrow["Bedrooms"] = colvals[12];
                            wlrow["Bathrooms"] = colvals[13];
                            wlrow["GarageSqft"] = colvals[14];
                            wlrow["Pool"] = colvals[15];
                            wlrow["AuctionDate"] = colvals[16];
                            wlrow["ListingStatus"] = "Unassigned";//colvals[17];
                            wlrow["EstimatedBid"] = colvals[18];
                            wlrow["OpeningBid"] = colvals[19];
                            wlrow["MaxBid"] = colvals[20];
                            wlrow["EstVal"] = colvals[21];
                            wlrow["Tax"] = colvals[22];
                            wlrow["Nos"] = colvals[23];
                            wlrow["Liens"] = colvals[24];
                            wlrow["OnOff"] = colvals[26];
                            wlrow["PropertyDetails"] = colvals[27];
                            wlrow["AssessorDesc"] = colvals[28];

                            wlrow["Source"] = colvals[33];
                            wlrow["DateRecorded"] = colvals[34];
                            wlrow["EscrowCompany"] = colvals[35];
                            wlrow["PrelistStatus"] = colvals[36];
                            wlrow["PurchasePrice"] = colvals[37];
                            wlrow["RehabCosts"] = colvals[38];
                            wlrow["MiscNotes"] = colvals[39];

                            PropertyId++;
                            //Add the rows to their table
                            wlTable.Rows.Add(wlrow);
                            status = true;
                            colvals.Clear();
                        }
                        else { eatLine = false; }
                    }
                }//end the using statement
            }
            catch (Exception ex)
            {
                status = false;
                throw new Exception("Error at line " + line + "<br/>" + ex.Message);
            }

            //Call the bulk insert functions here
            if (status.Equals(true))
            {
                //Check for valid returns
                if (BulkInsertSDREOTableCSV(wlTable))
                {
                    result = "Csv file imported to Working List.";
                }
            }
        }

        //Import into the Master List new parser for csv files
        public static void ImportLvReoMasterListCSV(string filepath)
        {
            string result = "Import failed";
            bool status = false;
            ArrayList colvals;
            string strLineText = string.Empty;
            bool eatLine = true;
            int line = 0;

            //Initialize tables with import format
            DataTable mlTable = ImportDataBuilders.CreateLvReoMasterTableCSV();
            DataTable parcelTable = ImportDataBuilders.CreateParcellTableCSV();

            List<string> apns = new List<string>();
            apns.AddRange(getApnLVREOMasterlist());

            try
            {
                //Build row objects for tables
                DataRow mlrow; DataRow parcelrow; DataRow apnrow; DataRow tsrow;

                using (StreamReader objReader = new StreamReader(filepath))
                {
                    while ((strLineText = objReader.ReadLine()) != null)
                    {
                        if (eatLine.Equals(false))
                        {
                            colvals = CSVParser(strLineText); //clean up rows
                            line++;
                            //Build new rows to add to the tables
                            mlrow = mlTable.NewRow(); parcelrow = parcelTable.NewRow(); 

                            //Add values to mlTable Table Row
                            mlrow["Trustee"] = colvals[1];
                            mlrow["TsNumber"] = colvals[2];
                            mlrow["APN"] = colvals[3];
                            if (apns.IndexOf(mlrow["APN"].ToString()) >= 0) continue;

                            mlrow["PropertyLocation"] = colvals[4];
                            mlrow["ZipCode"] = colvals[5];
                            mlrow["vo"] = colvals[6];
                            mlrow["hoa"] = colvals[8];
                            mlrow["AuctionDate"] = colvals[16];
                            mlrow["ListingStatus"] = "Unassigned";//colvals[17];
                            mlrow["EstimatedBid"] = colvals[18];
                            mlrow["MaxBid"] = colvals[20];
                            mlrow["EstVal"] = colvals[21];
                            mlrow["Tax"] = colvals[22];
                            mlrow["Nos"] = colvals[23];
                            mlrow["Liens"] = colvals[24];
                            mlrow["OnOff"] = colvals[26];
                            mlrow["PropertyDetails"] = colvals[27];
                            mlrow["ApnOpeningBid"] = colvals[19];
                            mlrow["TsOpeningBid"] = colvals[19];

                            mlrow["Source"] = colvals[33];
                            mlrow["DateRecorded"] = colvals[34];
                            mlrow["EscrowCompany"] = colvals[35];
                            mlrow["PrelistStatus"] = colvals[36];
                            mlrow["PurchasePrice"] = colvals[37];
                            mlrow["RehabCosts"] = colvals[38];
                            mlrow["MiscNotes"] = colvals[39];

                            //Add values to parcelTable Row
                            parcelrow["APN"] = colvals[3];
                            parcelrow["Stories"] = colvals[7];
                            parcelrow["ConstructYear"] = colvals[9];
                            parcelrow["TotalSqft"] = colvals[10];
                            parcelrow["LotSize"] = colvals[11];
                            parcelrow["Bedrooms"] = colvals[12];
                            parcelrow["Bathrooms"] = colvals[13];
                            parcelrow["GarageSqft"] = colvals[14];
                            parcelrow["Pool"] = colvals[15];
                            parcelrow["AssessorDesc"] = colvals[28];

                            //Add the rows to their table
                            mlTable.Rows.Add(mlrow); parcelTable.Rows.Add(parcelrow);
                            status = true;
                            colvals.Clear();
                        }
                        else { eatLine = false; }

                    }
                }//end the using statement
            }
            catch (Exception ex)
            {
                status = false;
                throw new Exception("Error at line " + line + "<br/>" + ex.Message);
            }

            //Call the bulk insert functions here
            if (status.Equals(true))
            {
                //Check for valid returns
                if (BulkInsertLVREOMlTableCSV(mlTable) && BulkInsertLVREOParcellTableCSV(parcelTable))
                {
                    result = "Csv file imported to Master List.";
                }
            }
        }

        //Cleans the CSV file line by line
        private static ArrayList CSVParser(string strInputString)
        {
            int intCounter = 0, intLenght;
            StringBuilder strElem = new StringBuilder();
            ArrayList alParsedCsv = new ArrayList();
            intLenght = strInputString.Length;
            strElem = strElem.Append("");
            int intCurrState = 0;
            int[][] aActionDecider = new int[9][];
            //Build the state array
            aActionDecider[0] = new int[4] { 2, 0, 1, 5 };
            aActionDecider[1] = new int[4] { 6, 0, 1, 5 };
            aActionDecider[2] = new int[4] { 4, 3, 3, 6 };
            aActionDecider[3] = new int[4] { 4, 3, 3, 6 };
            aActionDecider[4] = new int[4] { 2, 8, 6, 7 };
            aActionDecider[5] = new int[4] { 5, 5, 5, 5 };
            aActionDecider[6] = new int[4] { 6, 6, 6, 6 };
            aActionDecider[7] = new int[4] { 5, 5, 5, 5 };
            aActionDecider[8] = new int[4] { 0, 0, 0, 0 };
            for (intCounter = 0; intCounter < intLenght; intCounter++)
            {
                intCurrState = aActionDecider[intCurrState]
                                          [GetInputID(strInputString[intCounter])];
                //take the necessary action depending upon the state 
                PerformAction(ref intCurrState, strInputString[intCounter],
                             ref strElem, ref alParsedCsv);
            }
            //End of line reached, hence input ID is 3
            intCurrState = aActionDecider[intCurrState][3];
            PerformAction(ref intCurrState, '\0', ref strElem, ref alParsedCsv);
            return alParsedCsv;
        }

        //Used by CSVParser to clean up lines
        private static int GetInputID(char chrInput)
        {
            if (chrInput == '"')
            {
                return 0;
            }
            else if (chrInput == ',')
            {
                return 1;
            }
            else
            {
                return 2;
            }
        }

        //Used by CSVParser to clean up lines
        private static void PerformAction(ref int intCurrState, char chrInputChar,
                            ref StringBuilder strElem, ref ArrayList alParsedCsv)
        {
            string strTemp = null;
            switch (intCurrState)
            {
                case 0:
                    //Separate out value to array list
                    strTemp = strElem.ToString();
                    alParsedCsv.Add(strTemp);
                    strElem = new StringBuilder();
                    break;
                case 1:
                case 3:
                case 4:
                    //accumulate the character
                    strElem.Append(chrInputChar);
                    break;
                case 5:
                    //End of line reached. Separate out value to array list
                    strTemp = strElem.ToString();
                    alParsedCsv.Add(strTemp);
                    break;
                case 6:
                    //Erroneous input. Reject line.
                    alParsedCsv.Clear();
                    break;
                case 7:
                    //wipe ending " and Separate out value to array list
                    strElem.Remove(strElem.Length - 1, 1);
                    strTemp = strElem.ToString();
                    alParsedCsv.Add(strTemp);
                    strElem = new StringBuilder();
                    intCurrState = 5;
                    break;
                case 8:
                    //wipe ending " and Separate out value to array list
                    strElem.Remove(strElem.Length - 1, 1);
                    strTemp = strElem.ToString();
                    alParsedCsv.Add(strTemp);
                    strElem = new StringBuilder();
                    //goto state 0
                    intCurrState = 0;
                    break;
            }
        }

        //Bulk copy into the Working List table
        private static bool BulkInsertSDREOTableCSV(DataTable sourceTable)
        {
            SqlConnection myConnection = GetConnection();

            bool success = false;
            try
            {
                using (SqlBulkCopy s = new SqlBulkCopy(myConnection, SqlBulkCopyOptions.TableLock, null))
                {
                    s.DestinationTableName = "SdReo_MasterList"; //Sql database table name
                    s.BatchSize = sourceTable.Rows.Count;

                    //map the database columns to DataTable
                    s.ColumnMappings.Add("PropertyId", "PropertyId");
                    s.ColumnMappings.Add("Trustee", "Trustee");
                    s.ColumnMappings.Add("TsNumber", "TsNumber");
                    s.ColumnMappings.Add("APN", "Apn");
                    s.ColumnMappings.Add("PropertyLocation", "PropertyLocation");
                    s.ColumnMappings.Add("ZipCode", "ZipCode");
                    s.ColumnMappings.Add("vo", "vo");
                    s.ColumnMappings.Add("Stories", "Stories");
                    s.ColumnMappings.Add("hoa", "Hoa");
                    s.ColumnMappings.Add("ConstructYear", "ConstructYear");
                    s.ColumnMappings.Add("TotalSqft", "TotalSqft");
                    s.ColumnMappings.Add("LotSize", "LotSize");
                    s.ColumnMappings.Add("Bedrooms", "Bedrooms");
                    s.ColumnMappings.Add("Bathrooms", "Bathrooms");
                    s.ColumnMappings.Add("GarageSqft", "GarageSqft");
                    s.ColumnMappings.Add("Pool", "Pool");
                    s.ColumnMappings.Add("AuctionDate", "AuctionDate");
                    s.ColumnMappings.Add("ListingStatus", "ListingStatus");
                    s.ColumnMappings.Add("EstimatedBid", "EstimatedBid");
                    s.ColumnMappings.Add("MaxBid", "MaxBid");
                    s.ColumnMappings.Add("EstVal", "EstVal");
                    s.ColumnMappings.Add("Tax", "Tax");
                    s.ColumnMappings.Add("Nos", "Nos");
                    s.ColumnMappings.Add("Liens", "Liens");
                    s.ColumnMappings.Add("OnOff", "OnOff");
                    s.ColumnMappings.Add("PropertyDetails", "PropertyDetails");
                    s.ColumnMappings.Add("OpeningBid", "ApnOpeningBid");
                    s.ColumnMappings.Add("AssessorDesc", "AssessorDesc");

                    s.ColumnMappings.Add("Source", "Source");
                    s.ColumnMappings.Add("DateRecorded", "DateRecorded");
                    s.ColumnMappings.Add("EscrowCompany", "EscrowCompany");
                    s.ColumnMappings.Add("PrelistStatus", "PrelistStatus");
                    s.ColumnMappings.Add("PurchasePrice", "PurchasePrice");
                    s.ColumnMappings.Add("RehabCosts", "RehabCosts");
                    s.ColumnMappings.Add("MiscNotes", "MiscNotes");

                    myConnection.Open();
                    s.WriteToServer(sourceTable); //write to server
                    s.Close();
                }
                success = true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw;
            }
            finally
            {
                myConnection.Close();
            }

            return success;
        }

        //Bulk copy into the Working List table
        private static bool BulkInsertWlTableCSV(DataTable sourceTable)
        {
            SqlConnection myConnection = GetConnection();

            bool success = false;
            try
            {
                using (SqlBulkCopy s = new SqlBulkCopy(myConnection, SqlBulkCopyOptions.TableLock, null))
                {
                    s.DestinationTableName = "WorkingList"; //Sql database table name
                    s.BatchSize = sourceTable.Rows.Count;

                    //map the database columns to DataTable
                    s.ColumnMappings.Add("PropertyId", "PropertyId");
                    s.ColumnMappings.Add("Trustee", "Trustee");
                    s.ColumnMappings.Add("TsNumber", "TsNumber");
                    s.ColumnMappings.Add("APN", "Apn");
                    s.ColumnMappings.Add("PropertyLocation", "PropertyLocation");
                    s.ColumnMappings.Add("ZipCode", "ZipCode");
                    s.ColumnMappings.Add("vo", "vo");
                    s.ColumnMappings.Add("Stories", "Stories");
                    s.ColumnMappings.Add("hoa", "Hoa");
                    s.ColumnMappings.Add("ConstructYear", "ConstructYear");
                    s.ColumnMappings.Add("TotalSqft", "TotalSqft");
                    s.ColumnMappings.Add("LotSize", "LotSize");
                    s.ColumnMappings.Add("Bedrooms", "Bedrooms");
                    s.ColumnMappings.Add("Bathrooms", "Bathrooms");
                    s.ColumnMappings.Add("GarageSqft", "GarageSqft");
                    s.ColumnMappings.Add("Pool", "Pool");
                    s.ColumnMappings.Add("AuctionDate", "AuctionDate");
                    s.ColumnMappings.Add("ListingStatus", "ListingStatus");
                    s.ColumnMappings.Add("EstimatedBid", "EstimatedBid");
                    s.ColumnMappings.Add("MaxBid", "MaxBid");
                    s.ColumnMappings.Add("EstVal", "EstVal");
                    s.ColumnMappings.Add("Tax", "Tax");
                    s.ColumnMappings.Add("Nos", "Nos");
                    s.ColumnMappings.Add("Liens", "Liens");
                    s.ColumnMappings.Add("OnOff", "OnOff");
                    s.ColumnMappings.Add("PropertyDetails", "PropertyDetails");
                    s.ColumnMappings.Add("OpeningBid", "ApnOpeningBid");
                    s.ColumnMappings.Add("AssessorDesc", "AssessorDesc");

                    s.ColumnMappings.Add("Source", "Source");
                    s.ColumnMappings.Add("DateRecorded", "DateRecorded");
                    s.ColumnMappings.Add("EscrowCompany", "EscrowCompany");
                    s.ColumnMappings.Add("PrelistStatus", "PrelistStatus");
                    s.ColumnMappings.Add("PurchasePrice", "PurchasePrice");
                    s.ColumnMappings.Add("RehabCosts", "RehabCosts");
                    s.ColumnMappings.Add("MiscNotes", "MiscNotes");

                    myConnection.Open();
                    s.WriteToServer(sourceTable); //write to server
                    s.Close();
                }
                success = true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw;
            }
            finally
            {
                myConnection.Close();
            }

            return success;
        }

        //Bulk Copy into the Masterlist table from a csv file 
        private static bool BulkInsertMlTableCSV(DataTable sourceTable)
        {
            SqlConnection myConnection = GetConnection();

            bool success = false;
            try
            {
                using (SqlBulkCopy s = new SqlBulkCopy(myConnection, SqlBulkCopyOptions.TableLock, null))
                {
                    s.DestinationTableName = "MasterList"; //Sql database table name
                    s.BatchSize = sourceTable.Rows.Count;

                    //map the database columns to DataTable
                    s.ColumnMappings.Add("Trustee", "Trustee");
                    s.ColumnMappings.Add("TsNumber", "TsNumber");
                    s.ColumnMappings.Add("APN", "Apn");
                    s.ColumnMappings.Add("PropertyLocation", "PropertyLocation");
                    s.ColumnMappings.Add("ZipCode", "ZipCode");
                    s.ColumnMappings.Add("vo", "vo");
                    s.ColumnMappings.Add("hoa", "Hoa");
                    s.ColumnMappings.Add("AuctionDate", "AuctionDate");
                    s.ColumnMappings.Add("ListingStatus", "ListingStatus");
                    s.ColumnMappings.Add("EstimatedBid", "EstimatedBid");
                    s.ColumnMappings.Add("MaxBid", "MaxBid");
                    s.ColumnMappings.Add("EstVal", "EstVal");
                    s.ColumnMappings.Add("Tax", "Tax");
                    s.ColumnMappings.Add("Nos", "Nos");
                    s.ColumnMappings.Add("Liens", "Liens");
                    s.ColumnMappings.Add("OnOff", "OnOff");
                    s.ColumnMappings.Add("PropertyDetails", "PropertyDetails");

                    s.ColumnMappings.Add("Source", "Source");
                    s.ColumnMappings.Add("DateRecorded", "DateRecorded");
                    s.ColumnMappings.Add("EscrowCompany", "EscrowCompany");
                    s.ColumnMappings.Add("PrelistStatus", "PrelistStatus");
                    s.ColumnMappings.Add("PurchasePrice", "PurchasePrice");
                    s.ColumnMappings.Add("RehabCosts", "RehabCosts");
                    s.ColumnMappings.Add("MiscNotes", "MiscNotes");

                    myConnection.Open();
                    s.WriteToServer(sourceTable); //write to server
                    s.Close();
                }
                success = true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw;
            }
            finally
            {
                myConnection.Close();
            }

            return success;
        }

        //Bulk Copy into the Parcell table from a csv file
        private static bool BulkInsertParcellTableCSV(DataTable sourceTable)
        {
            SqlConnection myConnection = GetConnection();

            bool success = false;
            try
            {
                using (SqlBulkCopy s = new SqlBulkCopy(myConnection, SqlBulkCopyOptions.TableLock, null))
                {
                    s.DestinationTableName = "ParcellAppraisal"; //Sql database table name
                    s.BatchSize = sourceTable.Rows.Count;

                    //map the database columns to DataTable
                    s.ColumnMappings.Add("APN", "Apn");
                    s.ColumnMappings.Add("Stories", "Stories");
                    s.ColumnMappings.Add("ConstructYear", "ConstructYear");
                    s.ColumnMappings.Add("TotalSqft", "TotalSqft");
                    s.ColumnMappings.Add("LotSize", "LotSize");
                    s.ColumnMappings.Add("Bedrooms", "Bedrooms");
                    s.ColumnMappings.Add("Bathrooms", "Bathrooms");
                    s.ColumnMappings.Add("GarageSqft", "GarageSqft");
                    s.ColumnMappings.Add("Pool", "Pool");
                    s.ColumnMappings.Add("AssessorDesc", "AssessorDesc");

                    myConnection.Open(); 
                    s.WriteToServer(sourceTable); //write to server
                    s.Close();
                }
                success = true;
            }
            finally
            {
                myConnection.Close();
            }

            return success;
        }

        //Bulk Copy into the ApnBids table from a csv file
        private static bool BulkInsertApnTableCSV(DataTable sourceTable)
        {
            SqlConnection myConnection = GetConnection();

            bool success = false;
            try
            {
                using (SqlBulkCopy s = new SqlBulkCopy(myConnection, SqlBulkCopyOptions.TableLock, null))
                {
                    s.DestinationTableName = "OpeningBidsApn"; //Sql database table name
                    s.BatchSize = sourceTable.Rows.Count;

                    //map the database columns to DataTable
                    s.ColumnMappings.Add("Apn", "Apn");
                    s.ColumnMappings.Add("OpeningBid", "OpeningBid");
                    s.ColumnMappings.Add("ScrapeSource", "ScrapeSource");


                    myConnection.Open();
                    s.WriteToServer(sourceTable); //write to server
                    s.Close();
                }
                success = true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw;
            }
            finally
            {
                myConnection.Close();
            }

            return success;
        }

        //Bulk Copy into the TsBids table from a csv file
        private static bool BulkInsertTsTableCSV(DataTable sourceTable)
        {
            SqlConnection myConnection = GetConnection();

            bool success = false;
            try
            {
                using (SqlBulkCopy s = new SqlBulkCopy(myConnection, SqlBulkCopyOptions.TableLock, null))
                {
                    s.DestinationTableName = "OpeningBids"; //Sql database table name
                    s.BatchSize = sourceTable.Rows.Count;

                    //map the database columns to DataTable
                    s.ColumnMappings.Add("TsNumber", "TsNumber");
                    s.ColumnMappings.Add("OpeningBid", "OpeningBid");
                    s.ColumnMappings.Add("ScrapeSource", "ScrapeSource");


                    myConnection.Open(); 
                    s.WriteToServer(sourceTable); //write to server
                    s.Close();
                }
                success = true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw;
            }
            finally
            {
                myConnection.Close();
            }

            return success;
        }

        //Bulk Copy into the Masterlist table from a csv file 
        private static bool BulkInsertLVREOMlTableCSV(DataTable sourceTable)
        {
            SqlConnection myConnection = GetConnection();

            bool success = false;
            try
            {
                using (SqlBulkCopy s = new SqlBulkCopy(myConnection, SqlBulkCopyOptions.TableLock, null))
                {
                    s.DestinationTableName = "LvReo_MasterList"; //Sql database table name
                    s.BatchSize = sourceTable.Rows.Count;

                    //map the database columns to DataTable
                    s.ColumnMappings.Add("Trustee", "Trustee");
                    s.ColumnMappings.Add("TsNumber", "TsNumber");
                    s.ColumnMappings.Add("APN", "Apn");
                    s.ColumnMappings.Add("PropertyLocation", "PropertyLocation");
                    s.ColumnMappings.Add("ZipCode", "ZipCode");
                    s.ColumnMappings.Add("vo", "vo");
                    s.ColumnMappings.Add("hoa", "Hoa");
                    s.ColumnMappings.Add("AuctionDate", "AuctionDate");
                    s.ColumnMappings.Add("ListingStatus", "ListingStatus");
                    s.ColumnMappings.Add("EstimatedBid", "EstimatedBid");
                    s.ColumnMappings.Add("MaxBid", "MaxBid");
                    s.ColumnMappings.Add("EstVal", "EstVal");
                    s.ColumnMappings.Add("Tax", "Tax");
                    s.ColumnMappings.Add("Nos", "Nos");
                    s.ColumnMappings.Add("Liens", "Liens");
                    s.ColumnMappings.Add("OnOff", "OnOff");
                    s.ColumnMappings.Add("PropertyDetails", "PropertyDetails");
                    s.ColumnMappings.Add("ApnOpeningBid", "ApnOpeningBid");
                    s.ColumnMappings.Add("TsOpeningBid", "TsOpeningBid");

                    s.ColumnMappings.Add("Source", "Source");
                    s.ColumnMappings.Add("DateRecorded", "DateRecorded");
                    s.ColumnMappings.Add("EscrowCompany", "EscrowCompany");
                    s.ColumnMappings.Add("PrelistStatus", "PrelistStatus");
                    s.ColumnMappings.Add("PurchasePrice", "PurchasePrice");
                    s.ColumnMappings.Add("RehabCosts", "RehabCosts");
                    s.ColumnMappings.Add("MiscNotes", "MiscNotes");

                    myConnection.Open();
                    s.WriteToServer(sourceTable); //write to server
                    s.Close();
                }
                success = true;
            }
            finally
            {
                myConnection.Close();
            }

            foreach (DataRow dr in sourceTable.Rows)
            {
              try
              {
                UpdateLvReoAssmts(dr["Apn"].ToString());
              }
              catch { }
            }

            return success;
        }

        //Bulk Copy into the Parcell table from a csv file
        private static bool BulkInsertLVREOParcellTableCSV(DataTable sourceTable)
        {
            SqlConnection myConnection = GetConnection();

            bool success = false;
            try
            {
                using (SqlBulkCopy s = new SqlBulkCopy(myConnection, SqlBulkCopyOptions.TableLock, null))
                {
                    s.DestinationTableName = "LvReo_ParcellAppraisal"; //Sql database table name
                    s.BatchSize = sourceTable.Rows.Count;

                    //map the database columns to DataTable
                    s.ColumnMappings.Add("APN", "Apn");
                    s.ColumnMappings.Add("Stories", "Stories");
                    s.ColumnMappings.Add("ConstructYear", "ConstructYear");
                    s.ColumnMappings.Add("TotalSqft", "TotalSqft");
                    s.ColumnMappings.Add("LotSize", "LotSize");
                    s.ColumnMappings.Add("Bedrooms", "Bedrooms");
                    s.ColumnMappings.Add("Bathrooms", "Bathrooms");
                    s.ColumnMappings.Add("GarageSqft", "GarageSqft");
                    s.ColumnMappings.Add("Pool", "Pool");
                    s.ColumnMappings.Add("AssessorDesc", "AssessorDesc");

                    myConnection.Open();
                    s.WriteToServer(sourceTable); //write to server
                    s.Close();
                }
                success = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                myConnection.Close();
            }

            return success;
        }

        //Test for a database connection
        public static bool TestConnection()
        {
            bool connected = false;
            SqlConnection myConnection = GetConnection();

            try
            {
                myConnection.Open();
                connected = true;
            }
            catch (SqlException)
            {
                //Utilities.LogError(ex);
                throw;
            }
            finally
            {
                myConnection.Close();
            }
            return connected;
        }


        internal static string[] getApnMasterlistNewOnly()
        {
          SqlConnection myConnection = GetConnection();
          string sql = @"getApnMasterlistNewOnly";
          string[] apnNumbers;

          try
          {
            //Builds cmd obj from myConnection and set sql
            myConnection.Open();
            SqlCommand cmd = myConnection.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.StoredProcedure;

            //Create new datatable and fill it with the result
            DataTable dt = new DataTable();
            SqlDataAdapter adap = new SqlDataAdapter(cmd);
            adap.Fill(dt);

            apnNumbers = new string[dt.Rows.Count];
            int i = 0;

            //Loop and store values
            foreach (DataRow myRow in dt.Rows)
            {
              foreach (DataColumn myColumn in dt.Columns)
              {
                apnNumbers[i] = myRow[myColumn].ToString();
                i++;
              }
            }
          }
          catch (Exception ex)
          {
            throw ex;
          }
          finally
          {
            myConnection.Close();
          }
          return apnNumbers;
        }
    }//End Class DataAccess
}//End Namespace
